﻿@page "/openai"
@using System.Data;
@using System.Data.SqlClient;
@using Newtonsoft.Json;
@using RestSharp;

<div class="input-group mb-3">
    <div class="input-group-prepend">
        <span class="input-group-text">员工表</span>
    </div>
    <input class="form-control" @bind-value="Employee" placeholder="请输入员工表信息" />
</div>
<div class="input-group mb-3">
    <div class="input-group-prepend">
        <span class="input-group-text">部门表</span>
    </div>
    <input class="form-control" @bind-value="Department" placeholder="请输入部门表信息" />
</div>
<div class="input-group mb-3">
    <div class="input-group-prepend">
        <span class="input-group-text">员工薪水发放表</span>
    </div>
    <input class="form-control" @bind-value="Salary_Payments" placeholder="请输入部门薪水表信息" />
</div>
<div class="input-group mb-3">
    <div class="input-group-prepend">
        <span class="input-group-text">查询内容</span>
    </div>
    <input class="form-control" @bind-value="SearchContent" placeholder="请输入查询内容" />
</div>
<div class="input-group mb-3">
    <input class="form-control" @bind-value="ApiKey" placeholder="请输入你的OpenAI密钥" />
    <button class="btn btn-outline-secondary" @onclick="@Search">查询</button>
</div>



@if (IsLoading)
{
    <div class="progress">
        <div class="progress-bar progress-bar-striped" style="width: 100%"></div>
    </div>
}


@if (Table != null)
{
    <table class="table">
        <thead>
            <tr>
                @foreach (DataColumn item in Table.Columns)
                {
                    <td>
                        @item.ColumnName
                    </td>
                }
            </tr>
        </thead>
        <tbody>
            @foreach (DataRow row in Table.Rows)
            {
                <tr>
                    @for (int i = 0; i < Table.Columns.Count; i++)
                    {
                        <td>
                            @(row[i]?.ToString())
                        </td>
                    }
                </tr>
            }
        </tbody>
    </table>
}

<div>
    执行的SQL:
    <div class="form-group">
        <textarea class="form-control" row="10" style="height:300px">@ExecSQl</textarea>
    </div>
</div>

@code{
    private string ApiKey = "****************************";

    private string Employee = "Employee(id, name, department_id)";

    private string Department = "Department(id, name, address)";

    private string Salary_Payments = "Salary_Payments(id, employee_id, amount, date)";

    //private string SearchContent = "A query to list the names of the departments which employed more than 10 employees in the last 3 months";
    private string SearchContent = "";

    private bool IsLoading = false;

    private DataTable Table;

    private string ExecSQl;




    public async Task Search()
    {
        IsLoading = true;

        var aiRequst = $@"""
                ### Postgres SQL tables, with their properties:
                #
                # {Employee}
                # {Department}
                # {Salary_Payments}
                #
                ### {SearchContent}
                SELECT
                """;

        #region 请求AI获得SQL
        var client = new RestClient();
        string url = "https://timai.openai.azure.com/openai/deployments/text-davinci-003/completions?api-version=2022-12-01";
        var request = new RestRequest(url, Method.Post);
        request.AddHeader("Content-Type", "application/json");
        request.AddHeader("api-key", ApiKey);

        GptRequest gptRequest = new GptRequest
            {
                model = "text-davinci-003",
                prompt = aiRequst,
                temperature = 0,//控制随机性: 降低随机性会减少随机完成项。当温度接近零时，模型将变得确定性和重复性。
                max_tokens = 150,//对要在响应中生成的令牌数设置限制。系统最多支持在给定提示和响应完成之间共享的 2048 个令牌。(对于典型的英语文本，一个令牌大约为 4 个字符。)
                top_p = 1,//控制模型在通过 nucleus 采样生成响应时将考虑的令牌。如果将此值设置为 0.9，则最可能考虑所有可能的令牌的前 90%。当模型对评分最高的令牌的置信度较低时，此设置将避免使用明显不正确的令牌，同时仍保持多样性。
                frequency_penalty = 0,//根据令牌到目前为止在文本中出现的频率，按比例减少重复令牌的几率。这降低了在响应中重复完全相同的文本的可能性。
                presence_penalty = 0,//减少到目前为止文本中出现的任何标记重复的可能性。这增加了在响应中引入新主题的可能性。
                best_of = 1,//生成多个响应，并仅显示其所有令牌中总概率最大的响应。未使用的候选项仍会产生使用成本，因此请谨慎使用此参数，并确保同时设置最大响应长度和结束触发器的参数。请注意，仅当设置为 1 时，流式处理才有效
                stop = new string[] { "#", ";" }
            };
        request.AddParameter("application/json", gptRequest, ParameterType.RequestBody);
        var response = client.Execute(request);
        var aiResponse= JsonConvert.DeserializeObject<TextCompletion>(response.Content);
        ExecSQl = "select \"" + aiResponse?.choices?.FirstOrDefault()?.text;

        #endregion

        #region 查询数据

        //using SqlConnection connection = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=EFDemo;Integrated Security=True");
        //try
        //{
        //    connection.Open();
        //    using SqlCommand cmd = connection.CreateCommand();
        //    cmd.CommandText = ExecSQl;
        //    using SqlDataReader reader = cmd.ExecuteReader();
        //    Table = new DataTable();
        //    Table.Load(reader);
        //}
        //finally
        //{
        //    connection.Close();
        //}

        #endregion
        IsLoading = false;
        await InvokeAsync(StateHasChanged);
    }

    public class GptRequest
    {
        public string prompt { get; set; }
        public int max_tokens { get; set; }
        public double temperature { get; set; }
        public double frequency_penalty { get; set; }
        public double presence_penalty { get; set; }
        public double top_p { get; set; }
        public int best_of { get; set; }
        public string[] stop { get; set; }
        public string model { get; set; }
    }

    public class TextCompletion
    {
        public string id { get; set; }
        public string @object { get; set; }
        public long created { get; set; }
        public string model { get; set; }
        public List<CompletionChoice> choices { get; set; }
        public Usage usage { get; set; }
    }

    public class CompletionChoice
    {
        public string text { get; set; }
        public int index { get; set; }
        public string finish_reason { get; set; }
        public object logprobs { get; set; }
    }

    public class Usage
    {
        public int completion_tokens { get; set; }
        public int prompt_tokens { get; set; }
        public int total_tokens { get; set; }
    }
}